﻿'Description:试车通知单相关
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:longb
'create Date:2022-06-14
'Modify by longb
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient

Public Class TestRunBillBusiness


    ''' <summary>
    ''' 查询生产订单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMfgorder(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        Dim sqlStr As String = <string>SELECT MFGORDERID AS ID,MFGORDERNAME AS NAME,PLANNEDCOMPLETIONDATE,u.ATTRIBUTEVALUE,p1.PRODUCTFAMILYNAME
                                FROM MFGORDER M 
                                LEFT JOIN Userattribute u ON u.parentid=m.mfgorderid AND u.userattributename='发动机' 
                                LEFT JOIN PRODUCT p ON p.PRODUCTID =m.PRODUCTID
                                LEFT JOIN workflowproductfamilylist wfp ON wfp.workflowid=p.workflowid
                                LEFT JOIN PRODUCTFAMILY p1 ON p1.PRODUCTFAMILYID =wfp.productfamilylistid
                                left join mes_factory_mapping mfm on mfm.factoryid=m.FACTORYID
                                WHERE mfm.factorycode='603'       
                                </string>.Value
        sb.AppendLine(sqlStr)
        If para.ContainsKey("Name") Then
            If Not String.IsNullOrWhiteSpace(para("Name")) Then
                sb.AppendFormat(" and MFGORDERNAME like '%{0}%' ORDER BY PLANNEDCOMPLETIONDATE DESC", para("Name"))
            End If
        End If
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询责任单位
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetDutyWorkcenter(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>SELECT WORKCENTERID,WORKCENTERNAME FROM WORKCENTER WHERE 1=1</string>.Value)
        If para.ContainsKey("workcenterName") Then
            If Not String.IsNullOrWhiteSpace(para("workcenterName")) Then
                sb.AppendFormat(" and WORKCENTERNAME like '%{0}%' ", para("workcenterName"))
            End If
        End If
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询试车台
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetResourceDef(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>SELECT RESOURCEID,RESOURCENAME,d.DESCRIPTION FROM RESOURCEDEF d
                        left join mes_factory_mapping mfm on mfm.factoryid=d.factoryid
                        WHERE mfm.factorycode='603'  </string>.Value)
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询【总体】所有班长、组长
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTeamEmployees(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>SELECT e.EMPLOYEEID,e.EMPLOYEENAME,e.FULLNAME
                        FROM EMPLOYEE e 
                        LEFT JOIN EMPLOYEEROLE e2 ON e2.EMPLOYEEID =e.EMPLOYEEID 
                        LEFT JOIN ROLEDEF r ON r.ROLEID =e2.ROLEID
                        LEFT JOIN SESSIONVALUES s ON s.EMPLOYEEID=e.EMPLOYEEID 
                        left join mes_factory_mapping mfm on mfm.factoryid =s.FACTORYID 
                        WHERE (r.ROLENAME='班长' OR r.ROLENAME='组长') AND mfm.factorycode='603'</string>.Value)
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询员工信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEmployees(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                        SELECT e.EMPLOYEEID,e.EMPLOYEENAME,e.FULLNAME FROM EMPLOYEE e 
                        LEFT JOIN SESSIONVALUES s ON s.EMPLOYEEID=e.EMPLOYEEID
                      </string>)
        sb.AppendFormat("WHERE  s.FACTORYID ='{0}'", para("FactoryID"))
        If para.ContainsKey("EmployeeName") Then
            If Not String.IsNullOrWhiteSpace(para("EmployeeName")) Then
                sb.AppendFormat(" and e.EMPLOYEENAME like '%{0}%'", para("EmployeeName"))
            End If
        End If
        If para.ContainsKey("FullName") Then
            If Not String.IsNullOrWhiteSpace(para("FullName")) Then
                sb.AppendFormat(" and e.FULLNAME like '%{0}%'", para("FullName"))
            End If
        End If
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' 手动插入试车通知单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertNoticeBillData(ByVal para As Dictionary(Of String, String)) As Integer
        Dim sb As New StringBuilder
        sb.AppendLine(<string>INSERT INTO ZSTESTRUNNOTICEBILL
            (ZSTESTRUNNOTICEBILLID,ZSTESTRUNNOTICEBILLNO,MFGORDERID,ASSEMBLYWORKERID,ASSEMBLYDATE,DUTYWORKCENTERID,TESTBED,TESTRUNTYPE,ISUSE,BILLTYPE,STATUS,CREATEDATE,CREATEEMPLOYEEID,NOTES)</string>.Value)
        sb.AppendFormat("VALUES('{0}', '{1}', '{2}', '{3}', to_date('{4}','yyyy-MM-dd'), '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', {11}, '{12}', '{13}')",
                        para("ZSTESTRUNNOTICEBILLID"), para("ZSTESTRUNNOTICEBILLNO"), para("MFGORDERID"), para("ASSEMBLYWORKERID"), para("ASSEMBLYDATE"), para("DUTYWORKCENTERID"),
                        para("TESTBED"), para("TESTRUNTYPE"), "0", para("BILLTYPE"), "0", "sysdate", para("CREATEEMPLOYEEID"), para("NOTES"))
        Return DatabaseHelper.ExecuteSql(sb.ToString)
    End Function
    ''' <summary>
    ''' 自动插入试车通知单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function AutoInsertNoticeBillData(ByVal para As Dictionary(Of String, String)) As Integer
        Dim strSequence As String = "000000" + GetNoticeBillCount()
        Dim noticeBillNo As String = "SCT-" + DateTime.Now.ToString("yyyy") + "-" + para("FactoryCode") + "-" + strSequence.Substring(strSequence.Length - 4)
        Dim ZSTESTRUNNOTICEBILLID As String = Guid.NewGuid().ToString()
        Dim sb As New StringBuilder
        sb.AppendLine(<string>INSERT INTO ZSTESTRUNNOTICEBILL
            (ZSTESTRUNNOTICEBILLID,ZSTESTRUNNOTICEBILLNO,MFGORDERID,ISUSE,BILLTYPE,TESTRUNTYPE,STATUS,CREATEDATE,CREATEEMPLOYEEID,ASSEMBLYDATE)</string>.Value)
        sb.AppendFormat("VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}',{7},'{8}',{9})",
                        ZSTESTRUNNOTICEBILLID, noticeBillNo, para("MFGORDERID"), "0", "1", para("TESTRUNTYPE"), "0", "sysdate", para("CREATEEMPLOYEEID"), "sysdate")
        Return DatabaseHelper.ExecuteSql(sb.ToString)
    End Function
    ''' <summary>
    ''' 自动-插入试车返回单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function AutoInsertRetrunBillData(ByVal para As Dictionary(Of String, String)) As Integer
        Dim strSequence As String = "000000" + GetReturnBillCount()
        Dim returnBillNo As String = "SCF-" + DateTime.Now.ToString("yyyy") + "-" + para("FactoryCode") + "-" + strSequence.Substring(strSequence.Length - 4)
        Dim ZSTestRunReturnBillID As String = Guid.NewGuid().ToString()
        Dim sb As New StringBuilder
        sb.AppendLine(<string>INSERT INTO ZSTestRunReturnBill
            (ZSTestRunReturnBillID,ZSTestRunReturnBillNo,MFGORDERID,ISUSE,BILLTYPE,TESTRUNTYPE,STATUS,CREATEDATE,CREATEEMPLOYEEID,TESTFINISHDATE)</string>.Value)
        sb.AppendFormat("VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}',{7},'{8}',{9})",
                        ZSTestRunReturnBillID, returnBillNo, para("MFGORDERID"), "0", "1", para("TESTRUNTYPE"), "0", "sysdate", para("CREATEEMPLOYEEID"), "sysdate")
        Return DatabaseHelper.ExecuteSql(sb.ToString)
    End Function

    ''' <summary>
    ''' 删除试车通知单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DeleteNoticeBillData(ByVal para As Dictionary(Of String, String)) As Integer
        Dim sb As New StringBuilder
        sb.AppendFormat("UPDATE ZSTESTRUNNOTICEBILL SET ISUSE='1' WHERE BILLTYPE='2' AND STATUS='0' AND  ZSTESTRUNNOTICEBILLID IN('{0}')", para("ZSTestRunNoticeBillID").Replace(",", "','"))
        Return DatabaseHelper.ExecuteSql(sb.ToString)
    End Function
    ''' <summary>
    ''' 修改试车通知单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateNoticeBillData(ByVal para As Dictionary(Of String, String)) As Integer
        Dim sb As New StringBuilder
        sb.AppendLine("UPDATE ZSTESTRUNNOTICEBILL SET ")
        If para.ContainsKey("CREATEEMPLOYEEID") Then '保存更新
            sb.AppendFormat(" MFGORDERID='{0}',ASSEMBLYWORKERID='{1}',ASSEMBLYDATE=to_date('{2}','yyyy-MM-dd'),DUTYWORKCENTERID='{3}',TESTBED='{4}',TESTRUNTYPE='{5}',NOTES='{6}'",
                            para("MFGORDERID"), para("ASSEMBLYWORKERID"), para("ASSEMBLYDATE"), para("DUTYWORKCENTERID"), para("TESTBED"), para("TESTRUNTYPE"), para("NOTES"))
        End If
        If para.ContainsKey("AUDITEMPLOYEEID") Then '审核
            sb.AppendFormat(" STATUS='5',AUDITDATE=sysdate,AUDITEMPLOYEEID='{0}'", para("AUDITEMPLOYEEID"))
        End If
        If para.ContainsKey("RECIEVEEMPLOYEEID") Then '接收
            sb.AppendFormat("STATUS='10',RECIEVEDATE=sysdate,RECIEVEEMPLOYEEID='{0}'", para("RECIEVEEMPLOYEEID"))
        End If
        If para.ContainsKey("STATUS") Then '更新其他状态
            sb.AppendFormat("STATUS='{0}' ", para("STATUS"))
        End If
        sb.AppendFormat("WHERE ZSTESTRUNNOTICEBILLID IN ('{0}')", para("ZSTESTRUNNOTICEBILLID").Replace(",", "','"))
        Return DatabaseHelper.ExecuteSql(sb.ToString)
    End Function
    ''' <summary>
    ''' 分页查询--试车通知单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetNoticeBillData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine(<string>SELECT bill.ZSTESTRUNNOTICEBILLID,bill.ZSTESTRUNNOTICEBILLNO,m.MFGORDERNAME,u.ATTRIBUTEVALUE,
                        p1.PRODUCTFAMILYNAME,e.FULLNAME TeaMEMPLOYEE,w.WORKCENTERNAME,r.RESOURCENAME,e2.FULLNAME CreateEMPLOYEE,to_char(bill.CREATEDATE,'yyyy-MM-dd HH24:mi:ss') CREATEDATE,
                        e3.FULLNAME RECIEVEEEMPLOYEE,to_char(bill.RECIEVEDATE,'yyyy-MM-dd HH24:mi:ss') RECIEVEDATE,bill.NOTES,DECODE(bill.BILLTYPE,1,'自动生成','手动添加')BILLTYPE,
                        DECODE(bill.STATUS,'0','待提交','1','已提交','2','已退回','5','待接收','已接收') STATUS,
                        DECODE(bill.TESTRUNTYPE,'0','工厂试车','1','检验试车','2','附加试车','其他') TESTRUNTYPENAME,to_char(bill.ASSEMBLYDATE,'yyyy-MM-dd')ASSEMBLYDATE
                        ,to_char(bill.AUDITDATE,'yyyy-MM-dd HH24:mi:ss')AUDITDATE,e4.FULLNAME AUDITEMPLOYEE
                        ,BILL.MFGORDERID,bill.ASSEMBLYWORKERID,DUTYWORKCENTERID,bill.TESTBED,bill.TESTRUNTYPE 
                        FROM ZSTestRunNoticeBill bill
                        LEFT JOIN MFGORDER m ON m.MFGORDERID =BILL .MFGORDERID 
                        LEFT JOIN Userattribute u ON u.parentid=m.mfgorderid AND u.userattributename='发动机'
                        LEFT JOIN PRODUCT p ON p.PRODUCTID =m.PRODUCTID 
                        LEFT JOIN workflowproductfamilylist wfp ON wfp.workflowid=p.workflowid
                        LEFT JOIN PRODUCTFAMILY p1 ON p1.PRODUCTFAMILYID =wfp.productfamilylistid  
                        LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =bill.ASSEMBLYWORKERID 
                        LEFT JOIN WORKCENTER w ON w.WORKCENTERID =bill.DUTYWORKCENTERID 
                        LEFT JOIN RESOURCEDEF r ON r.RESOURCEID =bill.TESTBED 
                        LEFT JOIN EMPLOYEE e2 ON e2.EMPLOYEEID =bill.CREATEEMPLOYEEID 
                        LEFT JOIN EMPLOYEE e3 ON e3.EMPLOYEEID =bill.RECIEVEEMPLOYEEID
                        LEFT JOIN EMPLOYEE e4 ON e4.EMPLOYEEID =bill.AUDITEMPLOYEEID  
                        WHERE bill.ISUSE='0' </string>.Value)
        If para.ContainsKey("NoticeBillNo") Then
            If Not String.IsNullOrWhiteSpace(para("NoticeBillNo")) Then
                sb.AppendFormat(" and ZSTESTRUNNOTICEBILLNO like '%{0}%' ", para("NoticeBillNo"))
            End If
        End If
        If para.ContainsKey("Machine") Then
            If Not String.IsNullOrWhiteSpace(para("Machine")) Then
                sb.AppendFormat(" and u.ATTRIBUTEVALUE like '%{0}%' ", para("Machine"))
            End If
        End If
        If para.ContainsKey("MfgOrdername") Then
            If Not String.IsNullOrWhiteSpace(para("MfgOrdername")) Then
                sb.AppendFormat(" and m.MFGORDERNAME like '%{0}%' ", para("MfgOrdername"))
            End If
        End If
        If para.ContainsKey("Status") Then
            If Not String.IsNullOrWhiteSpace(para("Status")) Then
                sb.AppendFormat(" and bill.STATUS='{0}' ", para("Status"))
            End If
        End If
        If para.ContainsKey("startedate") Then
            If Not String.IsNullOrWhiteSpace(para("startedate")) Then
                sb.AppendFormat(" and to_char(bill.CREATEDATE,'yyyy-MM-dd')>='{0}' ", para("startedate"))
            End If
        End If
        If para.ContainsKey("enddate") Then
            If Not String.IsNullOrWhiteSpace(para("enddate")) Then
                sb.AppendFormat(" and to_char(bill.CREATEDATE,'yyyy-MM-dd')<='{0}' ", para("enddate"))
            End If
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 查询试车通知单--条数信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetNoticeBillCount() As String
        Dim Count = "1"
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT COUNT(1)+1 CN FROM ZSTestRunNoticeBill")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Count = dt.Rows("0")("CN").ToString
        End If
        Return Count
    End Function
    ''' <summary>
    ''' 查询试车返回单--条数信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetReturnBillCount() As String
        Dim Count = "1"
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT COUNT(1)+1 CN FROM ZSTestRunReturnBill")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Count = dt.Rows("0")("CN").ToString
        End If
        Return Count
    End Function

    ''' <summary>
    ''' 分页查询--试车返回单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetReturnBillData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine(<string>SELECT bill.ZSTESTRUNRETURNBILLID,bill.ZSTESTRUNRETURNBILLNO,m.MFGORDERNAME,u.ATTRIBUTEVALUE,
                        p1.PRODUCTFAMILYNAME,w.WORKCENTERNAME,r.RESOURCENAME,e2.FULLNAME CreateEMPLOYEE,to_char(bill.CREATEDATE,'yyyy-MM-dd HH24:mi:ss') CREATEDATE,
                        e3.FULLNAME RECIEVEEEMPLOYEE,to_char(bill.RECIEVEDATE,'yyyy-MM-dd HH24:mi:ss') RECIEVEDATE,bill.NOTES,DECODE(bill.BILLTYPE,1,'自动生成','手动添加')BILLTYPE,
                        DECODE(bill.STATUS,'0','待提交','1','待审核','2','已退回','5','待接收','10','已接收','已完成') STATUS,
                        DECODE(bill.TESTRUNTYPE,'0','工厂试车','1','检验试车','2','附加试车','其他') TESTRUNTYPENAME,to_char(bill.TestFinishDate,'yyyy-MM-dd')TESTFINISHDATE
                        ,to_char(bill.AUDITDATE,'yyyy-MM-dd HH24:mi:ss')AUDITDATE,e4.FULLNAME AUDITEMPLOYEE
                        ,BILL.MFGORDERID,DUTYWORKCENTERID,bill.TESTBED,bill.TESTRUNTYPE
                        ,TURBINENO, GREASEEXPEND, DYTCGX, DYTCQD, DYTCJQD, GYTCGX, GYTCYZ, GYTCJYF, TOTALWORKMINUTES, WORKMAXLARGESTATE, MAXLARGET, MAXLARGEF, MAXLARGEVIBRATE
                        FROM ZSTestRunReturnBill bill
                        LEFT JOIN MFGORDER m ON m.MFGORDERID =BILL .MFGORDERID 
                        LEFT JOIN Userattribute u ON u.parentid=m.mfgorderid AND u.userattributename='发动机'
                        LEFT JOIN PRODUCT p ON p.PRODUCTID =m.PRODUCTID 
                        LEFT JOIN workflowproductfamilylist wfp ON wfp.workflowid=p.workflowid
                        LEFT JOIN PRODUCTFAMILY p1 ON p1.PRODUCTFAMILYID =wfp.productfamilylistid 
                        LEFT JOIN WORKCENTER w ON w.WORKCENTERID =bill.DUTYWORKCENTERID 
                        LEFT JOIN RESOURCEDEF r ON r.RESOURCEID =bill.TESTBED 
                        LEFT JOIN EMPLOYEE e2 ON e2.EMPLOYEEID =bill.CREATEEMPLOYEEID 
                        LEFT JOIN EMPLOYEE e3 ON e3.EMPLOYEEID =bill.RECIEVEEMPLOYEEID
                        LEFT JOIN EMPLOYEE e4 ON e4.EMPLOYEEID =bill.AUDITEMPLOYEEID  
                        WHERE bill.ISUSE='0' </string>.Value)
        If para.ContainsKey("NoticeBillNo") Then
            If Not String.IsNullOrWhiteSpace(para("NoticeBillNo")) Then
                sb.AppendFormat(" and ZSTESTRUNNOTICEBILLNO like '%{0}%' ", para("NoticeBillNo"))
            End If
        End If
        If para.ContainsKey("Machine") Then
            If Not String.IsNullOrWhiteSpace(para("Machine")) Then
                sb.AppendFormat(" and u.ATTRIBUTEVALUE like '%{0}%' ", para("Machine"))
            End If
        End If
        If para.ContainsKey("MfgOrdername") Then
            If Not String.IsNullOrWhiteSpace(para("MfgOrdername")) Then
                sb.AppendFormat(" and m.MFGORDERNAME like '%{0}%' ", para("MfgOrdername"))
            End If
        End If
        If para.ContainsKey("Status") Then
            If Not String.IsNullOrWhiteSpace(para("Status")) Then
                sb.AppendFormat(" and bill.STATUS='{0}' ", para("Status"))
            End If
        End If
        If para.ContainsKey("startedate") Then
            If Not String.IsNullOrWhiteSpace(para("startedate")) Then
                sb.AppendFormat(" and to_char(bill.CREATEDATE,'yyyy-MM-dd')>='{0}' ", para("startedate"))
            End If
        End If
        If para.ContainsKey("enddate") Then
            If Not String.IsNullOrWhiteSpace(para("enddate")) Then
                sb.AppendFormat(" and to_char(bill.CREATEDATE,'yyyy-MM-dd')<='{0}' ", para("enddate"))
            End If
        End If
        If para.ContainsKey("ZSTESTRUNRETURNBILLID") Then
            If Not String.IsNullOrWhiteSpace(para("ZSTESTRUNRETURNBILLID")) Then
                sb.AppendFormat(" and ZSTESTRUNRETURNBILLID='{0}' ", para("ZSTESTRUNRETURNBILLID"))
            End If
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 手动插入试车返回单信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertReturnBillData(ByVal para As Dictionary(Of String, String)) As Integer
        Dim sb As New StringBuilder
        sb.AppendLine(<string>INSERT INTO ZSTestRunReturnBill
            (ZSTESTRUNRETURNBILLID,ZSTESTRUNRETURNBILLNO,MFGORDERID,TESTFINISHDATE,DUTYWORKCENTERID,TESTBED,TESTRUNTYPE,ISUSE,BILLTYPE,STATUS,CREATEDATE,CREATEEMPLOYEEID,
            TURBINENO, GREASEEXPEND, DYTCGX, DYTCQD, DYTCJQD, GYTCGX, GYTCYZ, GYTCJYF, TOTALWORKMINUTES, WORKMAXLARGESTATE, MAXLARGET, MAXLARGEF, MAXLARGEVIBRATE)</string>.Value)
        sb.AppendFormat("VALUES('{0}', '{1}', '{2}',to_date('{3}','yyyy-MM-dd'), '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', {10}, '{11}', '{12}', '{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}')",
                        para("ZSTESTRUNRETURNBILLID"), para("ZSTESTRUNRETURNBILLNO"), para("MFGORDERID"), para("TESTFINISHDATE"), para("DUTYWORKCENTERID"),
                        para("TESTBED"), para("TESTRUNTYPE"), "0", para("BILLTYPE"), "0", "sysdate", para("CREATEEMPLOYEEID"),
                        para("TURBINENO"), para("GREASEEXPEND"), para("DYTCGX"), para("DYTCQD"), para("DYTCJQD"), para("GYTCGX"), para("GYTCYZ"), para("GYTCJYF"), para("TOTALWORKMINUTES"), para("WORKMAXLARGESTATE"), para("MAXLARGET"), para("MAXLARGEF"), para("MAXLARGEVIBRATE"))
        Return DatabaseHelper.ExecuteSql(sb.ToString)
    End Function
    ''' <summary>
    ''' 更新返回单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateReturnBillData(ByVal para As Dictionary(Of String, String)) As Integer
        Dim sb As New StringBuilder
        sb.AppendLine("UPDATE ZSTestRunReturnBill SET ")
        If para.ContainsKey("CREATEEMPLOYEEID") Then '保存更新
            sb.AppendFormat(" MFGORDERID='{0}',TESTFINISHDATE=to_date('{1}','yyyy-MM-dd'),TESTBED='{2}',TESTRUNTYPE='{3}',TURBINENO='{4}',GREASEEXPEND='{5}',DYTCGX='{6}',DYTCQD='{7}',DYTCJQD='{8}',GYTCGX='{9}',GYTCYZ='{10}',GYTCJYF='{11}',TOTALWORKMINUTES='{12}',WORKMAXLARGESTATE='{13}',MAXLARGET='{14}',MAXLARGEF='{15}',MAXLARGEVIBRATE='{16}',DUTYWORKCENTERID='{17}'",
                            para("MFGORDERID"), para("TESTFINISHDATE"), para("TESTBED"), para("TESTRUNTYPE"), para("TURBINENO"),
                            para("GREASEEXPEND"), para("DYTCGX"), para("DYTCQD"), para("DYTCJQD"), para("GYTCGX"), para("GYTCYZ"), para("GYTCJYF"), para("TOTALWORKMINUTES"), para("WORKMAXLARGESTATE"), para("MAXLARGET"), para("MAXLARGEF"), para("MAXLARGEVIBRATE"), para("DUTYWORKCENTERID"))
        End If
        If para.ContainsKey("AUDITEMPLOYEEID") Then '审核
            sb.AppendFormat(" STATUS='5',AUDITDATE=sysdate,AUDITEMPLOYEEID='{0}'", para("AUDITEMPLOYEEID"))
        End If
        If para.ContainsKey("RECIEVEEMPLOYEEID") Then '接收
            sb.AppendFormat("STATUS='10',RECIEVEDATE=sysdate,RECIEVEEMPLOYEEID='{0}'", para("RECIEVEEMPLOYEEID"))
        End If
        If para.ContainsKey("STATUS") Then '更新其他状态
            sb.AppendFormat("STATUS='{0}' ", para("STATUS"))
        End If
        sb.AppendFormat("WHERE ZSTESTRUNRETURNBILLID IN ('{0}')", para("ZSTESTRUNRETURNBILLID").Replace(",", "','"))
        Return DatabaseHelper.ExecuteSql(sb.ToString)
    End Function
    ''' <summary>
    ''' 删除返回单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DeleteReturnBillData(ByVal para As Dictionary(Of String, String)) As Integer
        Dim sb As New StringBuilder
        sb.AppendFormat("UPDATE ZSTestRunReturnBill SET ISUSE='1' WHERE BILLTYPE='2' AND  STATUS='0' AND ZSTESTRUNRETURNBILLID IN('{0}')", para("ZSTESTRUNRETURNBILLID").Replace(",", "','"))
        Return DatabaseHelper.ExecuteSql(sb.ToString)
    End Function
    ''' <summary>
    ''' 删除成附件
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DeleteTRReturnCFBillData(ByVal para As Dictionary(Of String, String)) As Integer
        Dim sb As New StringBuilder
        sb.AppendFormat("DELETE FROM ZSTRReturnCFBill WHERE ZSTRETURNCFBILLID IN('{0}')", para("ZSTRETURNCFBILLID").Replace(",", "','"))
        Return DatabaseHelper.ExecuteSql(sb.ToString)
    End Function
    ''' <summary>
    ''' 删除未排除的故障信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DeleteTRReturnUFBillData(ByVal para As Dictionary(Of String, String)) As Integer
        Dim sb As New StringBuilder
        sb.AppendFormat("DELETE FROM ZSTRReturnUFBill WHERE ZSTRRETURNUFBILLID IN('{0}')", para("ZSTRRETURNUFBILLID").Replace(",", "','"))
        Return DatabaseHelper.ExecuteSql(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询未排除的故障信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTRReturnUFBillData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>SELECT ZSTRRETURNUFBILLID,SERIALNUMBER,FAULTTYPE,FAULTPERFORM,e.FULLNAME CREATENAME,to_char(CREATEDATE,'yyyy-MM-dd HH24:mi:ss') CREATEDATE,
            e2.FULLNAME EXCLUDENAME,to_char(EXCLUDEDATE,'yyyy-MM-dd HH24:mi:ss') EXCLUDEDATE,EXCLUDEMETHODS
            FROM ZSTRReturnUFBill a
            LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =a.CREATEEMPLOYEEID 
            LEFT JOIN EMPLOYEE e2 ON e2.EMPLOYEEID=a.EXCLUDEEMPLOYEEID
         </string>.Value)
        sb.AppendFormat(" WHERE 1=1")
        If para.ContainsKey("EXCLUDEMETHODS") Then
            If Not String.IsNullOrWhiteSpace(para("EXCLUDEMETHODS")) Then
                sb.AppendFormat(" and EXCLUDEMETHODS IS NULL")
            End If
        End If
        sb.AppendFormat(" AND ZSTESTRUNRETURNBILLID IN ('{0}') ORDER BY SERIALNUMBER", para("ZSTESTRUNRETURNBILLID").Replace(",", "','"))
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

End Class
